USE [DemoGraphics]
GO
/****** Object:  StoredProcedure [dbo].[InsertRecord]    Script Date: 06/03/2010 17:30:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[InsertRecord]
 @table_name VARCHAR(100),
 @state VARCHAR(2),
 @race VARCHAR(4),
 @couple VARCHAR(3),
 
 @field VARCHAR(25),
 @value VARCHAR(100)
AS
	DECLARE @SQL varchar(255)
	
	
	
	--checks if the inputs are valid
	-- if the inputs match up with constants
	IF EXISTS (SELECT * FROM states WHERE State = @state)
		AND EXISTS (SELECT * FROM Couples WHERE Couple = @couple)
		AND EXISTS (SELECT * FROM races WHERE Race = @race)
		AND EXISTS (SELECT * FROM Fields WHERE Field = @field)
	BEGIN
		--delete the record that matches the input
		SET @SQL = 'DELETE FROM ' + @table_name + ' WHERE State = '''
		SET @SQL = @SQL + @state + ''' AND race = '''
		SET @SQL = @SQL + @race + ''' AND couple =  '''
		SET @SQL = @SQL + @couple + ''' AND field = '''
		SET @SQL = @SQL + @field+ ''' '
		SELECT 'SQL2' = @SQL
		
		EXEC (@SQL)
		
		--and insert with the new results
		SET @SQL = 'INSERT INTO ' + @table_name + ' (State, Race, Couple, Field, Value) VALUES ('''
		SET @SQL = @SQL + @state + ''', '''
		SET @SQL = @SQL + @race + ''', '''
		SET @SQL = @SQL + @couple + ''', '''
		SET @SQL = @SQL + @field+ ''', '
		SET @SQL = @SQL + @value + ') '
		
		SELECT 'SQL' = @SQL
		EXEC(@SQL)
	END
	ELSE
	BEGIN
		-- Raise an error if inputs are invalid
		RAISERROR('The inputs are not formatted correctly according to the convention.', 16,1)
	END